package isae.c2.android.depense.DB;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import isae.c2.android.depense.DB.DepenseDBHelper;

public class TransactionDBAdapter {
	/** * Colonnes de la base de donnees*/
	public static final String KEY_ROWID = "_id";
	public static final String KEY_DATE = "vdate";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_AMOUNT = "amount";
	public static final String KEY_CAT = "cat_id";
	private static final String DATABASE_TABLE = "transactions";
	private Context context;
	private SQLiteDatabase database;
	private DepenseDBHelper dbHelper;
		
	public TransactionDBAdapter(Context context) {
		this.context = context;
	}
	
	/** * ouvrir la base de donnees*/
	public TransactionDBAdapter open() throws SQLException {
		dbHelper = new DepenseDBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	
	/** * fermer la base de donnees*/
	public void close() {
		dbHelper.close();
	}
	
	/** * cree une nouvelle transaction
 		* si cette transaction est cree, la valeur de retour est le numero de la ligne: RowId  
 		* sinon la valeur de retour sera -1 */	
	public long createTrx(String vdate, String description, String amount, long cat_id) {
		ContentValues initialValues = createContentValues(vdate, description, amount, cat_id);

		return database.insert(DATABASE_TABLE, null, initialValues);
	}
	
	/** * Mettre a jour une transaction */
	public boolean updateTrx(long rowId, String vdate, String description, String amount, long cat_id) {
		ContentValues updateValues = createContentValues(vdate, description, amount, cat_id);
		
		return database.update(DATABASE_TABLE, updateValues, KEY_ROWID + "="
					+ rowId, null) > 0;
		}

		
	/** * Supprimer une transaction */
	public boolean deleteTrx(long rowId) {
		return database.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}
		
	/** * Lire la liste des transactions par order de description*/
	public Cursor fetchAllTrx() {
		return database.query(DATABASE_TABLE, new String[] { KEY_ROWID,
				KEY_DATE, KEY_DESCRIPTION, KEY_AMOUNT, KEY_CAT }, null, null, null,
				null, KEY_DESCRIPTION);
	}

	/** * Lire la transaction situee a une certaine ligne "RowId"*/
	public Cursor fetchTrx(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, DATABASE_TABLE, new String[] {
				KEY_ROWID, KEY_DATE, KEY_DESCRIPTION, KEY_AMOUNT, KEY_CAT },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/** * mettre les parametres dans les colonnes convenables*/
	private ContentValues createContentValues(String vdate, String description, String amount, long cat_id) {
		ContentValues values = new ContentValues();
		values.put(KEY_DATE, vdate.toString());
		values.put(KEY_DESCRIPTION, description);
		values.put(KEY_AMOUNT, amount.toString());
		values.put(KEY_CAT, cat_id);
		return values;
	}
  /* La somme des revenus */
    public float getIncome() {
        
        Cursor cursor = database.rawQuery("SELECT SUM(amount) as sum FROM transactions "+
                "where cat_id in (select _id from category "+
                "where upper(cat_type) = upper(\"revenu\"))", null);
        
        cursor.moveToFirst();
        return cursor.getFloat(0);
    }
    
    /* La somme des depenses */
    public float getOutcome() {    
        Cursor cursor = database.rawQuery(    "select sum(amount)as sum from transactions " +
        "where cat_id not in (select _id from category " +
        "where upper(cat_type) = \"REVENU\")", null);
        
        cursor.moveToFirst();
        return cursor.getFloat(0);
    }
		
}
